Google スプレッドシートで管理しているデータを BigQuery から参照して SQL で集計してみた。

アドベントカレンダー2020

#Google BigQuery

#Google Cloud (GCP)

みかみ

2020.12.16

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、データアナリティクス事業本部のみかみです。

本エントリは、クラスメソッド BigQuery Advent Calendar 2020 の 16 日目のエントリです。 25日のアドベントカレンダー終了まで、弊社クラスメソッドデータアナリティクス事業本部のメンバーで、Google BigQuery に関する記事を紡いでいこうと思います。

突然ですが、エクセルって便利ですよね。エクセル相当の使い心地で使えて共有もできるスプレッドシートも便利ですよねぇ（個人的には、過去いろいろな家計簿アプリ使ってみましたが、結局スプレッドシートに落ち着いた思い出が。。

業務でもデータをエクセルやスプレッドシートで管理しているケースがあるのではないかと思います。 BigQuery ではスプレッドシートのデータに対して直接 SQL を実行できてしまうので、データ集計などに便利なんです！

Google ドライブデータのクエリ｜ BigQuery ドキュメント

やりたいこと

毎月開催している何かのセミナーの参加者をスプレッドシートで管理しているとします。スプレッドシートのデータは以下のような感じです。

なお、上記スプレッドシートのデータは、以下のテストデータ作成サイトで作成した架空の個人情報を元に必要な項目を追加したもので、実在の人物とは全く関係ございません。

TM - WebTools

このスプレッドシートのデータに BigQuery からアクセスして、出席率や同じ参加者が何回参加したか、DM 送信可能な参加者のメールアドレスなどの情報を SQL で取得したいケースを想定しました。

スプレッドシートを外部データソースとするテーブルを作成

BigQuery 管理画面から、スプレッドシートデータに SQL でアクセスするためのテーブルを作成します。

テーブル作成時に指定する必要があるため、スプレッドシートを開いた時にアドレスバーに表示される URL をメモっておきます。

テーブル作成画面「ソース」の「テーブルの作成元」で「ドライブ」を選択して、先ほどメモったスプレッドシートの URL を入力し、「ファイル形式」プルダウンから「Google スプレッドシート」を選択します。「送信先」で任意の「テーブル名」を入力し、「スキーマ」の「自動検出」チェックボックスをチェックします。ヘッダー行はスキップしたいので、「詳細オプション」を開いて「スキップするヘッダー行」に「1」を入力しました。

「テーブルを作成」ボタンをクリックすると、スプレッドシートを外部データソースとするテーブルができました。

SQL を実行して、データを確認してみます。

スプレッドシートと同じデータが、SQL で取得できました。

さらに、セミナーの出席者数と出席率を SQL で取得してみます。

期待通り、SQL でセミナー参加者を集計することができました。

なお、先ほどのテーブルではスプレッドシートのヘッダが日本語だったので、作成されたテーブルのカラム名は string_field_0 のように BigQuery が付与してくれましたが、スプレッドシートのヘッダーが英数文字列であれば、テーブルのカラムにも反映されます。また、カラムのデータ型も、スプレッドシートのデータに合わせて自動検出してくれます。

ためしに、以下のスプレッドシートを準備しました。

先ほどと同じように外部テーブルを作成します。

カラム名やデータ型にも、きちんとスプレッドシートデータが反映されていることが確認できました。

ちなみに、スプレッドシートの参照権限がない場合には、BigQuery からのアクセス時にも権限エラーになるのでご注意ください。

スプレッドシートデータを整形して BigQuery テーブルに格納

ものの 10 秒でスプレッドシートを SQL で参照できる非常に便利な機能ですが、スプレッドシートに複数のシートがある場合、残念ながら 1 シートのデータにしかアクセスできません。

セミナー参加者をスプレッドシートの複数のシートに分けて管理していた場合には、初めにシート数分の別ファイルに分割する必要があります。スプレッドシートのシート名を右クリックして、シートごとに新しいファイルを作成し、ファイル名を修正しました。

さらに、それぞれのファイルを外部データソースとする、10 月分、11 月分、12 月分のセミナー参加者テーブルを作成しました。スプレッドシートのファイル名を取得するために、今回はテーブル作成画面の「ファイル形式」で「Google スプレッドシート」ではなく「CSV」を選択しました。

また、スプレッドシートデータへの SQL 実行は、通常の BigQuery テーブルよりもかなり時間がかかります。前のセクションで動作確認した、10 行のセミナー参加者テーブルの出席率を確認するのに、3 秒ほど待たされました。。

そこで、先ほど作成した 3 つの外部テーブルのデータを、集計しやすいように整形して別テーブルに格納する、以下の SQL を実行します。

SELECT
  PARSE_DATE('%Y%m%d', SPLIT(fn,  '_')[OFFSET(2)]) AS seminer_date,
  string_field_0 AS user_name,
  string_field_1 AS mail,
  CASE WHEN string_field_2 = '○' THEN 1 ELSE 0 END AS flg_attend,
  CASE WHEN string_field_3 = '○' THEN 1 ELSE 0 END AS enable_dm
FROM (
  SELECT
    string_field_0,
    string_field_1,
    string_field_2,
    string_field_3,
    _FILE_NAME AS fn
  FROM
    dataset_advent_calendar.seminer_list_20201021
)
UNION DISTINCT
SELECT
  PARSE_DATE('%Y%m%d', SPLIT(fn,  '_')[OFFSET(2)]) AS seminer_date,
  string_field_0 AS user_name,
  string_field_1 AS mail,
  CASE WHEN string_field_2 = '○' THEN 1 ELSE 0 END AS flg_attend,
  CASE WHEN string_field_3 = '○' THEN 1 ELSE 0 END AS enable_dm
FROM (
  SELECT
    string_field_0,
    string_field_1,
    string_field_2,
    string_field_3,
    _FILE_NAME AS fn
  FROM
    dataset_advent_calendar.seminer_list_20201115
)
UNION DISTINCT
SELECT
  PARSE_DATE('%Y%m%d', SPLIT(fn,  '_')[OFFSET(2)]) AS seminer_date,
  string_field_0 AS user_name,
  string_field_1 AS mail,
  CASE WHEN string_field_2 = '○' THEN 1 ELSE 0 END AS flg_attend,
  CASE WHEN string_field_3 = '○' THEN 1 ELSE 0 END AS enable_dm
FROM (
  SELECT
    string_field_0,
    string_field_1,
    string_field_2,
    string_field_3,
    _FILE_NAME AS fn
  FROM
    dataset_advent_calendar.seminer_list_20201208
)

SQL を管理コンソールのクエリエディタに入力して実行します。やはりスプレッドシートのデータをそのまま取得する場合、かなり読み取り時間がかかり、処理負荷も増えていますね。。

SQL の実行結果が表示された後、「結果の保存」リンクから SELECT 結果を CSV ファイルや BigQuery テーブルなどに保存できるので、今回は「BigQuery テーブル」を選択して、新しいテーブルを作成します。

BigQuery テーブルが新しく作成され、先ほどの SELECT 結果が格納されました。

作成したテーブルに以下の SQL を実行して、各セミナー日ごとの出席人数と出席率を確認してみます。

SELECT
  seminer_date,
  SUM(flg_attend) AS at_count,
  SUM(flg_attend) / COUNT(*) * 100 AS at_rate
FROM
  dataset_advent_calendar.seminer_list_all
GROUP BY
  seminer_date
ORDER BY
  seminer_date

無事結果が取得できました。

外部テーブルに対して SQL を実行していた時と比べて、実行時間もだいぶ短くなりました！

まとめ（所感）

スプレッドシートのデータを分析に利用したい場合、他にも Dataflow や Dataprep を使って、データパイプラインを構築してデータを BigQuery に格納することもできます。

ですが、ただ SQL を実行したいだけなのに環境構築やらなにやらというのは、時間もかかるし、めんどくさい。。

今回はスプレッドシートのデータを BigQuery から参照してみましたが、スプレッドシート以外にも、Google ドライブに格納済みの CSV、JSONL、Avroファイルのデータを、同じように BigQuery から SQL で参照できます。外部テーブルを使えば BigQuery の画面 UI 操作だけですぐに SQL の実行やデータ格納が実現できるので、アドホック分析をしたい場合などに非常に便利ではないかと思いました！

明日 17 日目の BigQuery Advent Calendar 2020 は甲木からお送り予定です。また、今後のアドベントカレンダーの予定はこちらにも掲載しておりますので、引き続きお楽しみいただけますと幸いです。